package com.scalar.chiptrack.adaptor.ubicom.spil;

import com.scalar.chiptrack.jobs.JobHandler;
import com.scalar.chiptrack.adaptor.AdaptorConstants;
import com.scalar.chiptrack.adaptor.AdaptorUtils;
import com.scalar.chiptrack.adaptor.TestLotBinReport;
import com.scalar.chiptrack.adaptor.dao.TestAdaptorDAO;
import com.scalar.chiptrack.adaptor.dao.AdaptorRunInfo;
import com.scalar.chiptrack.utils.Logger;
import com.scalar.chiptrack.utils.StringUtils;
import com.scalar.ScalarException;

import java.util.*;
import java.io.File;

import jxl.*;

/**
 * User: ChandraSekhar
 * Date: Sep 26, 2007
 * Time: 4:34:09 PM
 */
public class SPILTestBinParser extends JobHandler implements AdaptorConstants
{
    private Logger m_parserLogger       = null;
    private String m_dataOutputDir      = null;

    public SPILTestBinParser() throws ScalarException
    {
        try
        {
            m_dataOutputDir = AdaptorUtils.getDataOutputDir(VENDOR_NAME_SPIL, WIP_TYPE_TEST);
            AdaptorUtils.makeAllAdaptorDirs( m_dataOutputDir );

            m_parserLogger = AdaptorUtils.getDataParserLogger( m_dataOutputDir,VENDOR_NAME_SPIL );
            TestAdaptorDAO.setLogger( m_parserLogger );
            AdaptorUtils.setLogger( m_parserLogger );
        }
        catch (ScalarException se)
        {
            log( "ERROR!! ERROR!! ERROR!! Exception while loading/creating SPIL Test Bin output directory from database, Exception: " + se );
            throw se;
        }
    }
    /**
     *
     * @param args
     * @throws Exception
     */
    public static void main( String args[] ) throws Exception
    {
        try
        {
            new SPILTestBinParser().execute();
        }
        catch (Exception e)
        {
            throw e;
        }
    }
    /**
     *
     * @throws Exception
     */
    public void execute() throws Exception
    {
        AdaptorUtils.printHeader(VENDOR_NAME_SPIL + " TEST BIN", CONSTANT_PARSER);

        File doneDestDir = new File( m_dataOutputDir + "/done" );

        File sourceDataoutDir = new File( m_dataOutputDir );

        File[] fileList = sourceDataoutDir.listFiles();
        if ( ( fileList == null ) || ( fileList.length <= 3 ) )
        {
            log( "No file found in directory: " + sourceDataoutDir );
            throw new ScalarException( "\n+-----------No file found in directory " + sourceDataoutDir );
        }

        ArrayList successFileList = new ArrayList();

        boolean success = false;

        for ( int i = 0; i < fileList.length; i++ )
        {
            if ( fileList[i].isDirectory() == true )
            {
                continue;
            }

            try
            {                   
                // Step 1
                // Parse Files to get Object List
                if (  fileList[i].getName() != null && fileList[i].getName().startsWith( SPILTestBinAdaptor.FILE_NAME_TEST_BIN ) )
                {
                    // To parse the the SPIL_WIP_STATUS sheet in to table
                    ArrayList testBinList = parseSpilFTBinReport( fileList[i].getAbsolutePath() );

                    if ( testBinList != null && testBinList.size() > 0 )
                    {
                        //to get the Spil Test Bin Details details
                        if ( testBinList.isEmpty() == false )
                        {
                            log( "Updating SPIL Binning Information" );
                            TestAdaptorDAO.saveTestBinInfo ( null, testBinList , VENDOR_NAME_SPIL );
                            success = true;
                        }
                        else
                        {
                            log( "SPIL Binning Information List is Empty" );
                        }
                    }

                    successFileList.add( fileList[i] );
                }

                if (  fileList[i].getName().startsWith( "Ubicom_Yield_report" ) )
                {
                    // To parse the the SPIL_YIELD_REPORT
                    ArrayList testBin1List = parseFTBinReport( fileList[i].getAbsolutePath() );


                    if ( testBin1List != null && testBin1List.size() > 0 )
                    {
                        //to get the Spil Test Bin Details details
                        if ( testBin1List.isEmpty() == false )
                        {
                            log( "Updating SPIL Binning Information" );
                            TestAdaptorDAO.saveTestBinInfo ( null, testBin1List , VENDOR_NAME_SPIL );
                            success = true;
                        }
                        else
                        {
                            log( "SPIL Binning Information List is Empty" );
                        }
                    }

                    successFileList.add( fileList[i] );
                }
            }
            catch ( Exception e )
            {
                success = false;
                log( "Exception while parsing and storing file: " + fileList[i].getAbsolutePath() + "\nException: " + e );
                e.printStackTrace();
                throw e;
            }
        }

         // TO SEND THE NEW LOTS, NEW TRANSIT LOTS, NO PO LOTS, DISCREPANCY LOTS NOTIFICATIONS TO THE USER
        //AdaptorDAO.sendAllNotifications(VENDOR_NAME_SPIL, true, true, true);

        // To Close the Drop Out Lots
        //AdaptorDAO.closeDropOutLots(VENDOR_NAME_SPIL);

        int adaptor_id = AdaptorRunInfo.getLastRunAdaptorId( SPILTestBinAdaptor.ADAPTOR_NAME, CONSTANT_ADAPTOR);

        // Update Adaptor_Run_Info table
        try
        {
            if ( success == true )
            {
                AdaptorRunInfo.saveAdaptorLastUpdatedStatus(CONSTANT_PARSER, SPILTestBinAdaptor.ADAPTOR_NAME, CONSTANT_SUCCESS, adaptor_id);
                System.out.println( "SUCCESSFULLY UPDATED IN THE DATABASE" );
            }
        }
        catch ( ScalarException se )
        {
            success = false; //As filES DATA SAVED , UPDATEING AS SUCCESS
            AdaptorRunInfo.saveAdaptorLastUpdatedStatus(CONSTANT_PARSER, SPILTestBinAdaptor.ADAPTOR_NAME, CONSTANT_SUCCESS, adaptor_id);
            log( "WARNING!! WARNING!! WARNING!! Could not update Adaptor run information, Exception: " + se );
            throw se;
         }
        // Move the successful files to 'done' directory
        if ( success )
        {
           AdaptorUtils.moveFilesToDone( successFileList, doneDestDir );
        }
    }
    /**
     *
     * @param fileName
     * @return
     * @throws Exception
     */
    private ArrayList parseSpilFTBinReport( String fileName ) throws Exception
    {
        if ( fileName == null ) throw new ScalarException( "Filename argument is null" );

        TestLotBinReport spilBinReport = null;
        ArrayList spilWipStatusList = new ArrayList();
        String lotNocheckValue;
        String partNocheckValue;
        try
        {
            File lotstatusFile = new File( fileName );
            if ( lotstatusFile.exists() == false )
            {
                throw new ScalarException( fileName + " doesnt exist" );
            }

            log( "\nFound " + fileName );
            log( "Parsing the file for Spil Test Bin Report..." );
            System.out.println("\nFound " + fileName);
            System.out.println("Parsing the file for Spil Test Bin Report...");

            Workbook workbook = Workbook.getWorkbook(lotstatusFile);
            Sheet sheet = workbook.getSheet(0);

            Cell[] row = null;

            for ( int rowCount = 5; rowCount < sheet.getRows(); rowCount++ )
            {
                row = sheet.getRow( rowCount );

                spilBinReport = new TestLotBinReport();

                lotNocheckValue = row[2].getContents().trim();
                partNocheckValue = row[0].getContents().trim();

                if( lotNocheckValue != null && lotNocheckValue.trim().length() > 0 && partNocheckValue != null && partNocheckValue.trim().length() > 0 )
                {
                    if (row.length > 0)
                    {
                        if (row[0].getType().equals(CellType.EMPTY))
                        {
                            spilBinReport.setDeviceName(null);
                        }
                        else
                        {
                            spilBinReport.setDeviceName(row[0].getContents().trim());
                        }
                    }
                    if (row.length > 1)
                    {
                        if (row[1].getType().equals(CellType.EMPTY))
                        {
                            spilBinReport.setPkgType( null );
                        }
                        else
                        {
                            spilBinReport.setPkgType(row[1].getContents().trim() );
                        }
                    }
                    if (row.length > 2)
                    {
                        if (row[2].getType().equals(CellType.EMPTY))
                        {
                            spilBinReport.setLotNumber( null);
                        }
                        else
                        {
                            spilBinReport.setLotNumber(row[2].getContents().trim());
                        }
                    }
                    if (row.length > 3)
                    {
                        if (row[3].getType().equals(CellType.EMPTY))
                        {
                            spilBinReport.setCustLotNo(null);
                        }
                        else
                        {
                            spilBinReport.setCustLotNo(row[3].getContents().trim());
                        }
                    }
                    if (row.length > 4)
                    {
                        if (row[4].getType().equals(CellType.EMPTY))
                        {
                            spilBinReport.setTesterId(null);
                        }
                        else
                        {
                            spilBinReport.setTesterId(row[4].getContents().trim());
                        }
                    }
                    if (row.length > 5)
                    {
                        if (row[5].getType().equals(CellType.EMPTY))
                        {
                            spilBinReport.setTestProg(null);
                        }
                        else
                        {
                            if ( row[5].getContents().indexOf("_") != -1 && row[5].getContents().lastIndexOf(".") != -1 )
                            {
                                spilBinReport.setTestProg ( row[5].getContents().trim().substring(row[5].getContents().indexOf("_")+1, row[5].getContents().lastIndexOf(".") ) );
                            }
                            else if (row[5].getContents().indexOf("_") != -1 && row[5].getContents().lastIndexOf(".") == -1 )
                            {
                                spilBinReport.setTestProg ( row[5].getContents().trim().substring(row[5].getContents().indexOf("_")+1, row[5].getContents().length() ) );
                            }

                        }
                    }
                   //6
                    if (row.length > 7)
                    {
                        if (row[7].getType().equals(CellType.EMPTY))
                        {
                            spilBinReport.setInQty(null);
                        }
                        else
                        {
                            spilBinReport.setInQty(row[7].getContents().trim());
                        }
                    }
                    if (row.length > 8)
                    {
                        if (row[8].getType().equals(CellType.EMPTY))
                        {
                            spilBinReport.setDateIn(null);
                        }
                        else
                        {
                            spilBinReport.setDateIn( row[8].getContents().trim());
                        }
                    }
                    if (row.length > 9)
                    {
                        if (row[9].getType().equals(CellType.EMPTY))
                        {
                            spilBinReport.setPassQty( null);
                        }
                        else
                        {
                            spilBinReport.setPassQty(row[9].getContents().trim());
                        }
                    }
                    //10, 11
                    if (row.length > 12)
                    {
                        if (row[12].getType().equals(CellType.EMPTY))
                        {
                            spilBinReport.setBin1(null);
                        }
                        else
                        {
                            spilBinReport.setBin1(row[12].getContents().trim());
                        }
                    }
                    if (row.length > 13)
                    {
                        if (row[13].getType().equals(CellType.EMPTY))
                        {
                            spilBinReport.setBin2(null);
                        }
                         else
                        {
                            spilBinReport.setBin2( row[13].getContents().trim() );
                        }
                    }
                    if (row.length > 14)
                    {
                        if (row[14].getType().equals(CellType.EMPTY))
                        {
                            spilBinReport.setBin3(null);
                        }
                        else
                        {
                            spilBinReport.setBin3(row[14].getContents().trim());
                        }
                    }
                    if (row.length > 15)
                    {
                        if (row[15].getType().equals(CellType.EMPTY))
                        {
                            spilBinReport.setBin4(null);
                        }
                        else
                        {
                            spilBinReport.setBin4(row[15].getContents().trim());
                        }
                    }
                    if (row.length > 16)
                    {
                        if (row[16].getType().equals(CellType.EMPTY))
                        {
                            spilBinReport.setBin5(null);
                        }
                        else
                        {
                            spilBinReport.setBin5(row[16].getContents().trim());
                        }
                    }
                    if (row.length > 17)
                    {
                        if ( row[17].getType().equals(CellType.EMPTY) )
                        {
                            spilBinReport.setBin6(null);
                        }
                        else
                        {
                            spilBinReport.setBin6( row[17].getContents().trim() );
                        }
                    }
                    if (row.length > 18)
                    {
                        if (row[18].getType().equals(CellType.EMPTY))
                        {
                            spilBinReport.setBin7(null);
                        }
                        else
                        {
                            spilBinReport.setBin7(row[18].getContents().trim());
                        }
                    }
                    if (row.length > 19)
                    {
                        if (row[19].getType().equals(CellType.EMPTY))
                        {
                            spilBinReport.setBin8(null);
                        }
                        else
                        {
                            spilBinReport.setBin8(row[19].getContents().trim());
                        }
                    }
                    if (row.length > 20)
                    {
                        if (row[20].getType().equals(CellType.EMPTY))
                        {
                            spilBinReport.setBin9(null);
                        }
                        else
                        {
                            spilBinReport.setBin9(row[20].getContents().trim());
                        }
                    }
                    if (row.length > 21)
                    {
                        if (row[21].getType().equals(CellType.EMPTY))
                        {
                            spilBinReport.setBin10(null);
                        }
                        else
                        {
                            spilBinReport.setBin10(row[21].getContents().trim());
                        }
                    }
                    if (row.length > 22)
                    {
                        if (row[22].getType().equals(CellType.EMPTY))
                        {
                            spilBinReport.setBin11(null);
                        }
                        else
                        {
                            spilBinReport.setBin11(row[22].getContents().trim());
                        }
                    }
                    if (row.length > 23)
                    {
                        if (row[23].getType().equals(CellType.EMPTY))
                        {
                            spilBinReport.setBin12(null);
                        }
                        else
                        {
                            spilBinReport.setBin12(row[23].getContents().trim());
                        }
                    }
                    if (row.length > 24)
                    {
                        if (row[24].getType().equals(CellType.EMPTY))
                        {
                            spilBinReport.setBin13(null);
                        }
                        else
                        {
                            spilBinReport.setBin13(row[24].getContents().trim());
                        }
                    }
                    if (row.length > 25)
                    {
                        if (row[25].getType().equals(CellType.EMPTY))
                        {
                            spilBinReport.setBin14(null);
                        }
                        else
                        {
                            spilBinReport.setBin14(row[25].getContents().trim());
                        }
                    }
                    if (row.length > 26)
                    {
                        if (row[26].getType().equals(CellType.EMPTY))
                        {
                            spilBinReport.setBin15(null);
                        }
                        else
                        {
                           spilBinReport.setBin15(row[26].getContents().trim());
                        }
                    }

                    if (row.length > 32)
                    {
                        if (row[32].getType().equals(CellType.EMPTY))
                        {
                            spilBinReport.setDateCode(null);
                        }
                        else
                        {
                            spilBinReport.setDateCode(row[32].getContents().trim());
                        }
                    }
                    
                    spilWipStatusList.add( spilBinReport );
                }
            }
        }
        catch (Exception ex)
        {
            ex.printStackTrace();
            throw ex;
        }
        return spilWipStatusList;

    }


    private ArrayList parseFTBinReport( String fileName ) throws Exception
    {
        if ( fileName == null ) throw new ScalarException( "Filename argument is null" );

        TestLotBinReport spilBinReport = null;
        ArrayList spilWipStatusList = new ArrayList();
        String lotNocheckValue;
        String partNocheckValue;
        try
        {
            File lotstatusFile = new File( fileName );
            if ( lotstatusFile.exists() == false )
            {
                throw new ScalarException( fileName + " doesnt exist" );
            }

            log( "\nFound " + fileName );
            log( "Parsing the file for Spil Test Bin Yield Report..." );
            System.out.println("\nFound " + fileName);
            System.out.println("Parsing the file for Spil Test Bin Yield Report...");

            Workbook workbook = Workbook.getWorkbook(lotstatusFile);
            Sheet sheet = workbook.getSheet(0);

            Cell[] row = null;

            for ( int rowCount = 5; rowCount < sheet.getRows(); rowCount++ )
            {
                row = sheet.getRow( rowCount );

                spilBinReport = new TestLotBinReport();

                lotNocheckValue = row[2].getContents().trim();
                partNocheckValue = row[1].getContents().trim();

                if( lotNocheckValue != null && lotNocheckValue.trim().length() > 0 && partNocheckValue != null && partNocheckValue.trim().length() > 0 )
                {
                    //0 is CUSTOMER
                    if (row.length > 1)
                    {
                        if (row[1].getType().equals(CellType.EMPTY))
                        {
                            spilBinReport.setDeviceName(null);
                        }
                        else
                        {
                            spilBinReport.setDeviceName(row[1].getContents().trim());
                        }
                    }
                    if (row.length > 2)
                    {
                        if (row[2].getType().equals(CellType.EMPTY))
                        {
                            spilBinReport.setLotNumber( null );
                        }
                        else
                        {
                            spilBinReport.setLotNumber(row[2].getContents().trim() );
                        }
                    }
                    
                    if (row.length > 3)
                    {
                        if (row[3].getType().equals(CellType.EMPTY))
                        {
                            spilBinReport.setCustLotNo(null);
                        }
                        else
                        {
                            spilBinReport.setCustLotNo(row[3].getContents().trim());
                        }
                    }

                    if (row.length > 4)
                    {
                        if (row[4].getType().equals(CellType.EMPTY))
                        {
                            spilBinReport.setDateIn(null);
                        }
                        else if( row[4].getType().equals(CellType.DATE) )
                        {
                            DateCell dc = (DateCell)row[4];
						    spilBinReport.setDateIn(StringUtils.formatDateToString(dc.getDate() ) );
                        }
                        else
                        {
                            spilBinReport.setDateIn( row[4].getContents().trim());
                        }
                    }
                    if (row.length > 5)
                    {
                        if (row[5].getType().equals(CellType.EMPTY))
                        {
                            spilBinReport.setPkgType(null);
                        }
                        else
                        {
                            spilBinReport.setPkgType( row[5].getContents().trim() );
                        }
                    }
                    if (row.length > 6)
                    {
                        if (row[6].getType().equals(CellType.EMPTY))
                        {
                            spilBinReport.setInQty(null);
                        }
                        else
                        {
                            spilBinReport.setInQty(row[6].getContents().trim());
                        }
                    }
                    if (row.length > 7)
                    {
                        if (row[7].getType().equals(CellType.EMPTY))
                        {
                            spilBinReport.setPassQty( null );
                        }
                        else
                        {
                            spilBinReport.setPassQty( row[7].getContents().trim() );
                        }
                    }
                    //8 is YIELD , WE ARE CALCULATING THIS
                    if (row.length > 9)
                    {
                        if (row[9].getType().equals(CellType.EMPTY))
                        {
                            spilBinReport.setOsQty( null );
                        }
                        else
                        {
                            spilBinReport.setOsQty( row[9].getContents().trim() );
                        }
                    }
                    if (row.length > 10)
                    {
                        if (row[10].getType().equals(CellType.EMPTY))
                        {
                            spilBinReport.setOSRate( null );
                        }
                        else
                        {
                            spilBinReport.setOSRate(row[10].getContents().trim().replaceAll("%", "") );
                        }
                    }
                    if (row.length > 11)
                    {
                        if (row[11].getType().equals(CellType.EMPTY))
                        {
                            spilBinReport.setDamageQty(null);
                        }
                         else
                        {
                            spilBinReport.setDamageQty( row[11].getContents().trim() );
                        }
                    }
                    if (row.length > 12)
                    {
                        if (row[12].getType().equals(CellType.EMPTY))
                        {
                            spilBinReport.setLossQty(null);
                        }
                        else
                        {
                            spilBinReport.setLossQty(row[12].getContents().trim());
                        }
                    }
                    if (row.length > 13)
                    {
                        if (row[13].getType().equals(CellType.EMPTY))
                        {
                            spilBinReport.setTesterId(null);
                        }
                        else
                        {
                            spilBinReport.setTesterId(row[13].getContents().trim());
                        }
                    }
                    if (row.length > 14)
                    {
                        if (row[14].getType().equals(CellType.EMPTY))
                        {
                            spilBinReport.setHandler(null);
                        }
                        else
                        {
                            spilBinReport.setHandler( row[14].getContents().trim() );
                        }
                    }
                    //SKIP 5 Columns for RC2_QTY To RC6_QTY
                    if (row.length > 20)
                    {
                        if ( row[20].getType().equals(CellType.EMPTY) )
                        {
                            spilBinReport.setBin1(null);
                        }
                        else
                        {
                            spilBinReport.setBin1( row[20].getContents().trim() );
                        }
                    }
                    if (row.length > 21)
                    {
                        if (row[21].getType().equals(CellType.EMPTY))
                        {
                            spilBinReport.setBin2(null);
                        }
                        else
                        {
                            spilBinReport.setBin2(row[21].getContents().trim());
                        }
                    }
                    if (row.length > 22)
                    {
                        if (row[22].getType().equals(CellType.EMPTY))
                        {
                            spilBinReport.setBin3(null);
                        }
                        else
                        {
                            spilBinReport.setBin3(row[22].getContents().trim());
                        }
                    }
                    if (row.length > 23)
                    {
                        if (row[23].getType().equals(CellType.EMPTY))
                        {
                            spilBinReport.setBin4(null);
                        }
                        else
                        {
                            spilBinReport.setBin4(row[23].getContents().trim());
                        }
                    }
                    if (row.length > 24)
                    {
                        if (row[24].getType().equals(CellType.EMPTY))
                        {
                            spilBinReport.setBin5(null);
                        }
                        else
                        {
                            spilBinReport.setBin5(row[24].getContents().trim());
                        }
                    }
                    if (row.length > 25)
                    {
                        if (row[25].getType().equals(CellType.EMPTY))
                        {
                            spilBinReport.setTestProg(null);
                        }
                        else
                        {
                            spilBinReport.setTestProg(row[25].getContents().trim());
                        }
                    }
                    if (row.length > 26)
                    {
                        if (row[26].getType().equals(CellType.EMPTY))
                        {
                            spilBinReport.setDateCode(null);
                        }
                        else
                        {
                            spilBinReport.setDateCode(row[26].getContents().trim());
                        }
                    }
                    if (row.length > 27)
                    {
                        if (row[27].getType().equals(CellType.EMPTY) || "#N/A".equalsIgnoreCase( row[27].getContents().trim() ) || row[27].getContents().trim().startsWith("ERROR") )
                        {
                            spilBinReport.setScanInQty(null);
                        }
                        else
                        {                               
                            spilBinReport.setScanInQty(row[27].getContents().trim());
                        }
                    }
                    if (row.length > 28)
                    {
                        if (row[28].getType().equals(CellType.EMPTY) || "#N/A".equalsIgnoreCase( row[28].getContents().trim() ) || row[28].getContents().trim().startsWith("ERROR")  )
                        {
                            spilBinReport.setScanOutQty(null);
                        }
                        else
                        {
                            spilBinReport.setScanOutQty(row[28].getContents().trim());
                        }
                    }
                    /*if (row.length > 29)
                    {
                        if (row[29].getType().equals(CellType.EMPTY) || "#N/A".equalsIgnoreCase( row[29].getContents().trim() ) )
                        {
                            spilBinReport.setScanYield( null );
                        }
                        else
                        {
                           spilBinReport.setScanYield(row[29].getContents().trim().replaceAll("%", "") );
                        }
                    }*/
                                       

                    spilWipStatusList.add( spilBinReport );
                }
            }
        }
        catch (Exception ex)
        {
            ex.printStackTrace();
            throw ex;
        }
        return spilWipStatusList;

    }
    private void log( String message )
    {
        if ( message == null ) return;

        if ( m_parserLogger != null )
        {
            m_parserLogger.logDebug( message + LINE_SEPARATOR );
        }
        else
        {
            System.out.println( message );
        }
    } // end of log method
}

